PIVOT clause

Overview

The PIVOT clause is a SQL feature that transforms rows into columns, making it easier to create summary tables and cross-tabulations of your data. DuckDB's implementation of PIVOT is more streamlined than traditional databases, eliminating the need to explicitly specify the values you want to pivot on.

DuckDB Syntax

In DuckDB, the basic syntax is:

Copy code
PIVOT dataset ON column_to_pivot USING aggregation_function

Examples

First, let's create some sample sales data:

Copy code
CREATE TABLE sales AS 
SELECT * FROM (
  VALUES 
    ('2023-01', 'Electronics', 1000),
    ('2023-01', 'Clothing', 800),
    ('2023-02', 'Electronics', 1200),
    ('2023-02', 'Clothing', 900),
    ('2023-03', 'Electronics', 950),
    ('2023-03', 'Clothing', 850)
) AS t(month, category, amount);

Basic pivot example to show sales by category across months:

Copy code
PIVOT sales 
ON month 
USING sum(amount);

Output:

Copy code
┌─────────────┬─────────┬─────────┬─────────┐
│  category   │ 2023-01 │ 2023-02 │ 2023-03 │
│   varchar   │ int128  │ int128  │ int128  │
├─────────────┼─────────┼─────────┼─────────┤
│ Electronics │    1000 │    1200 │     950 │
│ Clothing    │     800 │     900 │     850 │
└─────────────┴─────────┴─────────┴─────────┘

You can also use multiple aggregations in the same pivot:

Copy code
PIVOT sales 
ON month 
USING sum(amount) AS total, avg(amount) AS average;

Comparison to Other Databases

In traditional databases like PostgreSQL or SQL Server, you typically need to:

1. Explicitly list the values you want to pivot on
2. Write more verbose syntax with multiple subqueries
3. Sometimes use conditional aggregation with CASE statements

DuckDB simplifies this by automatically detecting the distinct values to pivot on and providing a more intuitive syntax. This makes it particularly useful for exploratory data analysis where you might not know all possible values in advance.

Common Use Cases

The PIVOT clause is particularly useful for:

• Creating financial reports with months or quarters as columns
• Analyzing survey responses with responses as columns
• Building cross-tabulation reports
• Converting long/narrow data formats to wide formats for visualization
• Creating pivot tables similar to those in spreadsheet applications